package org.mcflier.appuntamento.io.driver.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import org.mcflier.appuntamento.core.exception.PropertiesException;
import org.mcflier.appuntamento.core.logger.LoggerManager;
import org.mcflier.appuntamento.data.model.AlternativeTitle;
import org.mcflier.appuntamento.data.model.Genre;
import org.mcflier.appuntamento.data.model.Movie;
import org.mcflier.appuntamento.data.model.Production;
import org.mcflier.appuntamento.i18n.Country;
import org.mcflier.appuntamento.i18n.Language;
import org.mcflier.appuntamento.io.exception.JDBCConnectionException;
import org.mcflier.appuntamento.io.exception.JDBCStatementException;
import org.mcflier.appuntamento.io.exception.JDBCTransactionException;

public class JDBCDriver extends AbstractJDBCDriver {
	
	private static JDBCDriver instance;
	
	static{
		instance = null;
	}

	public static JDBCDriver getInstance() throws PropertiesException{
		if(instance == null)
			instance = new JDBCDriver();
		
		return instance;
	}
	
	private Connection conn = null;
	private Statement stmt = null;
	private Logger logger;
	
	public JDBCDriver() throws PropertiesException {
		super();
		this.logger = LoggerManager.getInstance().getLogger(this.getClass().getName());
	}
	
	private void prepareConnection() throws JDBCConnectionException, JDBCStatementException{
		if(this.conn == null){
			try {
				this.conn = DriverManager.getConnection(
						this.getProperties().getProperty(JDBC_URL_KEY),
						this.getProperties().getProperty(USER_KEY),
						(this.getProperties().getProperty(PASSWORD_KEY))==null?"":this.getProperties().getProperty(PASSWORD_KEY));
				
				this.logger.info("JDBC Connection created: "+this.conn);
			} catch (SQLException e) {
				throw new JDBCConnectionException(e);
			}
		}
		else{
			try {
				if(this.conn.isClosed()){
					this.conn = null;
					this.logger.info("JDBC Connection is closed. Creating a new one");
					this.prepareConnection();
				}
				else
					this.logger.info("JDBC Connection ready: "+this.conn);
			} catch (SQLException e) {
				throw new JDBCConnectionException("Error asking if connection opened: "+this.conn);
			}
		}
		
		try {
			if(this.stmt == null || this.stmt.isClosed())
				this.stmt = this.conn.createStatement();
		} catch (SQLException e) {
			throw new JDBCStatementException("Error creating the statement", e);
		}
	}
	
	private ResultSet executeQuery(String query) throws JDBCConnectionException, JDBCStatementException{
		this.prepareConnection();
		try {
			//Statement stmt = this.conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			this.logger.info("Executed query: "+query);
			//stmt.close(); //This element must not be closed before the ResultSet is used
			return rs;
		} catch (SQLException e) {
			throw new JDBCStatementException("Error creating jdbc statement", e);
		}
	}
	
	private int executeUpdate(String update) throws JDBCConnectionException, JDBCStatementException{
		this.prepareConnection();
		try {
			//Statement stmt = this.conn.createStatement();
			int rows = stmt.executeUpdate(update);
			this.logger.info("Executed update with "+rows+" row(s) affected: "+update);
			//stmt.close();
			return rows;
		} catch (SQLException e) {
			throw new JDBCStatementException("Error creating jdbc statement", e);
		}
	}
	
	public void closeConnection() throws JDBCConnectionException{
		try {
			if(this.conn!=null && !this.conn.isClosed()){
				this.conn.close();
				this.logger.info("Closing jdbc connection: "+this.conn);
			}
		} catch (SQLException e) {
			throw new JDBCConnectionException("Error closing jdbc connection: "+this.conn, e);
		}
	}
	
	private void beginTransaction() throws JDBCTransactionException{
		try {
			this.conn.setAutoCommit(false);
		} catch (SQLException e) {
			throw new JDBCTransactionException(this.getClass().getName()+": Error beginning the transaction. Disabling autocommit failed", e);
		}
	}
	
	private void commit() throws JDBCTransactionException{
		try {
			this.conn.commit();
		} catch (SQLException e) {
			throw new JDBCTransactionException(this.getClass().getName()+": Error commiting", e);
		}
		try {
			this.conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new JDBCTransactionException(this.getClass().getName()+": Error commiting. Enabling autocommit failed", e);
		}
	}
	
	private void rollback() throws JDBCTransactionException{
		try {
			this.conn.rollback();
		} catch (SQLException e) {
			throw new JDBCTransactionException(this.getClass().getName()+": Error rollback", e);
		}
		try {
			this.conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new JDBCTransactionException(this.getClass().getName()+": Error commiting. Enabling autocommit failed", e);
		}
	}
	
	private int linkMovieWithGenres(Movie movie) throws JDBCConnectionException, JDBCStatementException{
		List<Genre> genres = movie.getGenres();
		StringBuilder sb;
		ResultSet rs;
		int results = 0;
		
		for (Genre genre : genres) {
			sb = new StringBuilder("SELECT count(*) FROM ")
			.append(MOVIE_GENRE_TABLE).append(" WHERE ")
			.append(MOVIE_GENRE_IDMOVIE).append("='").append(movie.getId()).append("' AND ")
			.append(MOVIE_GENRE_IDGENRE).append("=").append(genre.getId());
			
			try {
				rs = this.executeQuery(sb.toString());
				if(rs.next()){
					if(rs.getInt(1)==0){
						rs.close();

						try {
							sb = new StringBuilder("INSERT INTO ").append(MOVIE_GENRE_TABLE)
									.append(" (").append(MOVIE_GENRE_IDMOVIE).append(", ").append(MOVIE_GENRE_IDGENRE).append(") VALUES ('")
									.append(movie.getId()).append("', ").append(genre.getId()).append(");");
							if(this.executeUpdate(sb.toString())>0){
								results++;
								this.logger.info("Linked film: "+movie+" with genre: "+genre);
							}
							else{
								this.logger.warning("NOT linked film: "+movie+" with genre: "+genre);
							}
						} catch (JDBCConnectionException e) {
							this.logger.warning("Error linking movie '"+movie+"' and genre: "+genre+"\n"+e.getMessage());
							throw e;
						} catch (JDBCStatementException e) {
							this.logger.warning("Error linking movie '"+movie+"' and genre: "+genre+"\n"+e.getMessage());
							throw e;
						}
					}
				}
			} catch (JDBCConnectionException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and genre"+genre+"\n"+e.getMessage());
				throw e;
			} catch (JDBCStatementException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and genre"+genre+"\n"+e.getMessage());
				throw e;
			} catch (SQLException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and genre"+genre+"\n"+e.getMessage());
				throw new JDBCStatementException(e);
			}
		}
		
		return results;
	}
	
	private int linkMovieWithProductions(Movie movie) throws JDBCConnectionException, JDBCStatementException{
		List<Production> productions = movie.getProductions();
		StringBuilder sb;
		ResultSet rs;
		int results = 0;
		
		for (Production production : productions) {
			sb = new StringBuilder("SELECT count(*) FROM ")
			.append(MOVIE_PRODUCTION_TABLE).append(" WHERE ")
			.append(MOVIE_PRODUCTION_IDMOVIE).append("='").append(movie.getId()).append("' AND ")
			.append(MOVIE_PRODUCTION_IDPRODUCTION).append("=").append(production.getId());
			
			try {
				rs = this.executeQuery(sb.toString());
				if(rs.next() && rs.getInt(1)==0){
					rs.close();
					
					try {
						sb = new StringBuilder("INSERT INTO ").append(MOVIE_PRODUCTION_TABLE)
								.append(" (").append(MOVIE_PRODUCTION_IDMOVIE).append(", ").append(MOVIE_PRODUCTION_IDPRODUCTION).append(") VALUES ('")
								.append(movie.getId()).append("', ").append(production.getId()).append(");");
						if(this.executeUpdate(sb.toString())>0){
							results++;
							this.logger.info("Linked film: "+movie+" with production: "+production);
						}
						else{
							this.logger.warning("NOT linked film: "+movie+" with production: "+production);
						}
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking movie '"+movie+"' and production: "+production+"\n"+e.getMessage());
						throw e;
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking movie '"+movie+"' and production: "+production+"\n"+e.getMessage());
						throw e;
					}
				}
				else{
					rs.close();
					this.logger.warning("NOT linked film: "+movie+" with production: "+production);
				}
			} catch (JDBCConnectionException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and production: "+production+"\n"+e.getMessage());
				throw e;
			} catch (JDBCStatementException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and production: "+production+"\n"+e.getMessage());
				throw e;
			} catch (SQLException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and production: "+production+"\n"+e.getMessage());
				throw new JDBCStatementException(e);
			}
		}
		
		return results;
	}
	
	private int linkMovieWithProductionCountries(Movie movie) throws JDBCConnectionException, JDBCStatementException{
		List<Country> countries = movie.getProductionCountries();
		StringBuilder sb;
		ResultSet rs;
		int results = 0;
		
		for (Country country : countries) {
			sb = new StringBuilder("SELECT count(*) FROM ")
			.append(COUNTRY_PRODUCTION_TABLE).append(" WHERE ")
			.append(COUNTRY_PRODUCTION_IDMOVIE).append("='").append(movie.getId()).append("' AND ")
			.append(COUNTRY_PRODUCTION_COUNTRY_CODE).append("='").append(country.getCode()).append("'");
			
			try {
				rs = this.executeQuery(sb.toString());
				if(rs.next() && rs.getInt(1)==0){
					rs.close();
					
					try {
						sb = new StringBuilder("INSERT INTO ").append(COUNTRY_PRODUCTION_TABLE)
								.append(" (").append(COUNTRY_PRODUCTION_IDMOVIE).append(", ").append(COUNTRY_PRODUCTION_COUNTRY_CODE).append(") VALUES ('")
								.append(movie.getId()).append("', '").append(country.getCode()).append("');");
						if(this.executeUpdate(sb.toString())>0){
							results++;
							this.logger.info("Linked film: "+movie+" with production country: "+country);
						}
						else{
							this.logger.warning("NOT linked film: "+movie+" with production country: "+country);
						}
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking movie '"+movie+"' and production country: "+country+"\n"+e.getMessage());
						throw e;
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking movie '"+movie+"' and production country: "+country+"\n"+e.getMessage());
						throw e;
					}
				}
				else{
					rs.close();
					this.logger.warning("NOT linked film: "+movie+" with production country: "+country);
				}
			} catch (JDBCConnectionException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and production country: "+country+"\n"+e.getMessage());
				throw e;
			} catch (JDBCStatementException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and production country: "+country+"\n"+e.getMessage());
				throw e;
			} catch (SQLException e) {
				this.logger.warning("Error checking if exists a relation between movie "+movie+" and production country: "+country+"\n"+e.getMessage());
				throw new JDBCStatementException(e);
			}
		}
		
		return results;
	}
	
	private boolean linkMovieLanguageDependantContent(Movie movie) throws JDBCStatementException, JDBCConnectionException{
		StringBuilder sb;
		ResultSet rs;
		boolean result = false;
		
		//Checking the movie has not this language-dependant information associated
		sb = new StringBuilder("SELECT count(*) FROM ")
		.append(MOVIE_LANGUAGE_TABLE).append(" WHERE ")
		.append(MOVIE_LANGUAGE_IDMOVIE).append("='").append(movie.getId()).append("' AND ")
		.append(MOVIE_LANGUAGE_LANGUAGE_CODE).append("='").append(movie.getAlternativeName().getLanguage().getCode()).append("'");

		try {
			rs = this.executeQuery(sb.toString());
			if(rs.next() && rs.getInt(1)==0){
				rs.close();

				try {
					sb = new StringBuilder("INSERT INTO ").append(MOVIE_LANGUAGE_TABLE)
							.append(" (").append(MOVIE_LANGUAGE_IDMOVIE).append(", ")
							.append(MOVIE_LANGUAGE_LANGUAGE_CODE).append(", ")
							.append(MOVIE_LANGUAGE_ALTERNATIVE_TITLE).append(", ")
							.append(MOVIE_LANGUAGE_OVERVIEW).append(", ")
							.append(MOVIE_LANGUAGE_TAGLINE)
							.append(") VALUES ('")
							.append(movie.getId()).append("', '")
							.append(movie.getAlternativeName().getLanguage().getCode()).append("', '")
							.append(movie.getAlternativeName().getAlternativeTitle()).append("', '")
							.append(movie.getOverview()).append("', '")
							.append(movie.getTagline()).append("');");


					if(this.executeUpdate(sb.toString())>0){
						this.logger.info("Linked film: "+movie+" with language: "+movie.getAlternativeName().getLanguage());
						result = true;
					}
					else{
						this.logger.warning("NOT linked film: "+movie+" with language: "+movie.getAlternativeName().getLanguage());
						result = false;
					}
				} catch (JDBCConnectionException e) {
					this.logger.warning("Error linking movie '"+movie+"' and language: "+movie.getAlternativeName().getLanguage()+"\n"+e.getMessage());
					throw e;
				} catch (JDBCStatementException e) {
					this.logger.warning("Error linking movie '"+movie+"' and language: "+movie.getAlternativeName().getLanguage()+"\n"+e.getMessage());
					throw e;
				}
			}
			else{
				this.logger.warning("NOT linked film: "+movie+" with language: "+movie.getAlternativeName().getLanguage());
				return false;
			}
		} catch (JDBCConnectionException e) {
			this.logger.warning("Error checking if exists a relation between movie "+movie+" and language: "+movie.getAlternativeName().getLanguage()+"\n"+e.getMessage());
			throw e;
		} catch (JDBCStatementException e) {
			this.logger.warning("Error checking if exists a relation between movie "+movie+" and language: "+movie.getAlternativeName().getLanguage()+"\n"+e.getMessage());
			throw e;
		} catch (SQLException e) {
			this.logger.warning("Error checking if exists a relation between movie "+movie+" and language: "+movie.getAlternativeName().getLanguage()+"\n"+e.getMessage());
			throw new JDBCStatementException(e);
		}
		
		return result;
	}
	
	private boolean linkMovieWithTracingInformation(Movie movie) throws JDBCConnectionException, JDBCStatementException{
		if(movie.isTracing()){
			StringBuilder sb = new StringBuilder("INSERT INTO ");
			sb.append(TRACING_TABLE).append(" (")
			.append(TRACING_IDMOVIE).append(", ")
			.append(TRACING_ADDEDBY).append(", ")
			.append(TRACING_ADDEDATE).append(", ")
			.append(TRACING_TRACINGLANGUAGE).append(", ")
			.append(TRACING_TRACED).append(", ")
			.append(TRACING_TRACEDDATE).append(", ")
			.append(TRACING_SOURCE).append(", ")
			.append(TRACING_LINK).append(") VALUES ('")
			.append(movie.getId()).append("', '")
			.append(movie.getAddedBy()).append("', '")
			.append(movie.getAddedDate()).append("', '")
			.append((movie.getTracingLanguage()==null)?null:movie.getTracingLanguage().getCode()).append("', '")
			.append(movie.isTraced()).append("', '")
			.append(movie.getTracedDate()).append("', '")
			.append(movie.getTracedSource()).append("', '")
			.append(movie.getDownloadLink()).append("');");

			if(this.executeUpdate(sb.toString())>0){
				this.logger.info("Linked tracing information to the movie: "+movie);
				return true;
			}
			else{
				this.logger.info("Tracing information cannot be linked to the movie: "+movie);
				return false;
			}
		}
		else{
			this.logger.info("The movie: '"+movie+"' is not being traced");
			return false;
		}
	}
	
	/**
	 * This method just add the movie information in the movie table, without genres, productions ... even without language-dependant information
	 * @param movie - The adding movie
	 * @return True if the movie has been added, false otherwise
	 * @throws JDBCStatementException 
	 * @throws JDBCConnectionException 
	 */
	private boolean justAddAMovie(Movie movie) throws JDBCStatementException, JDBCConnectionException{
		StringBuilder sb = new StringBuilder("INSERT INTO ").append(MOVIE_TABLE).append(" (")
				.append(MOVIE_IDMOVIE).append(", ")
				.append(MOVIE_ORIGINAL_TITLE).append(", ")
				.append(MOVIE_POSTER_PATH).append(", ")
				.append(MOVIE_HOMEPAGE).append(", ")
				.append(MOVIE_RUNTIME).append(", ")
				.append(MOVIE_RELEASE_DATE).append(", ")
				.append(") VALUES ('")
				.append(movie.getId()).append("', '")
				.append(movie.getOriginalName()).append("', '")
				.append((movie.getPosterPath()==null)?"NULL":movie.getPosterPath()).append("', '")
				.append((movie.getHomepage()==null)?"NULL":movie.getHomepage()).append("', ")
				.append(movie.getRuntime()).append(", '")
				.append((movie.getReleaseDate()==null)?"NULL":movie.getReleaseDate()).append("')");
		
		int updateResult = this.executeUpdate(sb.toString());
		
		if(updateResult>0) this.logger.info("Added just the information for the movie: "+movie);
		else this.logger.warning("Not added the information for the movie: "+movie);
		
		return updateResult>0;
	}
	
	@Override
	public boolean addMovie(Movie movie, boolean useTransaction) throws JDBCTransactionException, JDBCConnectionException, JDBCStatementException {
		if(movie == null)
			throw new NullPointerException(this.getClass().getName()+": Adding movie parameter must not be null");

		if(!this.existsMovie(movie.getId())){
			try{
				if(useTransaction){
					this.prepareConnection();
					this.beginTransaction();
				}
				if(this.justAddAMovie(movie)){
					this.addGenres(movie.getGenres());
					try {
						this.linkMovieWithGenres(movie);
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its genres");
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its genres");
					}

					this.addProductions(movie.getProductions());
					try {
						this.linkMovieWithProductions(movie);
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its productions");
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its productions");
					}
					
					try {
						this.linkMovieWithProductionCountries(movie);
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its production countries");
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its production countries");
					}
					
					try {
						this.linkMovieWithTracingInformation(movie);
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its tracing information");
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking the movie: '"+movie+"' with its tracing information");
					}

					try {
						this.linkMovieLanguageDependantContent(movie);
					} catch (JDBCStatementException e) {
						this.logger.warning("Error linking the movie '"+movie+"' with its language-dependant information. "+((useTransaction)?"This error forces the undone of all changes.":"This error can cause inconsistence data"));
						if(useTransaction)
							this.rollback();
					} catch (JDBCConnectionException e) {
						this.logger.warning("Error linking the movie '"+movie+"' with its language-dependant information. "+((useTransaction)?"This error forces the undone of all changes.":"This error can cause inconsistence data"));
						if(useTransaction)
							this.rollback();
					}
					
					if(useTransaction) this.commit();
					this.logger.info("The movie '"+movie+"' has been stored");
					return true;
				}
				else{
					if(useTransaction) this.rollback();
					this.logger.warning("The movie: "+movie+" has not been stored");
					return false;
				}
			}
			catch(JDBCStatementException except){
				if(useTransaction) this.rollback();
				this.logger.warning("Error storing the movie: "+movie+((useTransaction==true)?" Rollback done.":" Not transaction used. Data may be inconsistent"));
				throw except;
			}
		}
		else{
			this.logger.info("The movie: '"+movie+"' is already stored. Checking if there is new language-dependant content and adding it in case");
			
			try {
				this.linkMovieLanguageDependantContent(movie);
			} catch (JDBCStatementException e) {
				this.logger.warning("Error linking the movie '"+movie+"' with its language-dependant information. "+((useTransaction)?"This error forces the undone of all changes.":"This error can cause inconsistence data"));
				if(useTransaction)
					this.rollback();
			} catch (JDBCConnectionException e) {
				this.logger.warning("Error linking the movie '"+movie+"' with its language-dependant information. "+((useTransaction)?"This error forces the undone of all changes.":"This error can cause inconsistence data"));
				if(useTransaction)
					this.rollback();
			}
			return false;
		}
	}

	@Override
	public int addMovies(List<Movie> movies) {
		//Not use the method existMovies for removing duplicated movies
		//because there is language-dependant information, so there may be
		//some duplicated movies (not its language-dependant information)
		
		int result = 0;
		for (Movie movie : movies) {
			try {
				if(this.addMovie(movie, true)) result++;
			} catch (JDBCTransactionException e) {
				this.logger.warning("Error adding movie: '"+movie+"'\n"+e.getMessage());
			} catch (JDBCConnectionException e) {
				this.logger.warning("Error adding movie: '"+movie+"'\n"+e.getMessage());
			} catch (JDBCStatementException e) {
				this.logger.warning("Error adding movie: '"+movie+"'\n"+e.getMessage());
			}
		}
		
		if(result == movies.size()) this.logger.info("Added all the movies");
		else this.logger.warning("Added only "+result+" of "+movies.size());
		
		return result;
	}

	@Override
	public boolean existsMovie(String id) throws JDBCConnectionException, JDBCStatementException {
		boolean result = false;
		ResultSet rs;
		
		StringBuilder sb = new StringBuilder("SELECT count(*) FROM ");
		sb.append(MOVIE_TABLE).append(" WHERE ")
		.append(MOVIE_IDMOVIE).append("='").append(id).append("';");
		
		try {
			rs = this.executeQuery(sb.toString());
			if(rs.next())
				result = rs.getInt(1)>0;
			else
				result = false;
			rs.close();
		} catch (SQLException e) {
			throw new JDBCStatementException("Error while checking if a movie is stored", e);
		}
		
		return result;
	}

	@Override
	public List<String> existMovies(List<String> ids) throws JDBCConnectionException, JDBCStatementException {
		//Not used the method existGenre because it's not optimized for iterations

		if(ids !=null){
			ResultSet rs;
			List<String> existingIds = new ArrayList<String>();
			if(ids.size()==0) return existingIds;

			StringBuilder sb = new StringBuilder("SELECT ");
			sb.append(MOVIE_IDMOVIE).append(" FROM ").append(MOVIE_TABLE)
			.append(" WHERE ").append(MOVIE_IDMOVIE).append(" IN (");
			for (String id : ids) {
				sb.append("'").append(id).append("',");
			}
			sb.deleteCharAt(sb.length()-1);//delete the last ,
			sb.append(");");

			try {
				rs = this.executeQuery(sb.toString());
				while(rs.next()){
					existingIds.add(rs.getString(1));
				}

				rs.close();
				return existingIds;
			} catch (SQLException e) {
				throw new JDBCStatementException("Error checking existing movies", e);
			}
		}
		else
			throw new IllegalArgumentException("Checking existing movies error. The id list must not be null or empty");
	}

	@Override
	public Movie getMovie(String idMovie, String codeLanguage) throws JDBCConnectionException, JDBCStatementException {
		if(this.existsMovie(idMovie)){
			StringBuilder sb = new StringBuilder("SELECT ");
			sb.append("m.").append(MOVIE_IDMOVIE).append(", ")
			.append("m.").append(MOVIE_ORIGINAL_TITLE).append(", ")
			.append("m.").append(MOVIE_POSTER_PATH).append(", ")
			.append("m.").append(MOVIE_HOMEPAGE).append(", ")
			.append("m.").append(MOVIE_RUNTIME).append(", ")
			.append("m.").append(MOVIE_RELEASE_DATE).append(", ")
			.append("l.").append(LANGUAGE_CODE).append(", ")
			.append("l.").append(LANGUAGE_NAME).append(", ")
			.append("ml.").append(MOVIE_LANGUAGE_OVERVIEW).append(", ")
			.append("ml.").append(MOVIE_LANGUAGE_ALTERNATIVE_TITLE).append(", ")
			.append("ml.").append(MOVIE_LANGUAGE_TAGLINE)
			.append(" FROM ").append(MOVIE_TABLE).append(" m,")
			.append(LANGUAGE_TABLE).append(" l,")
			.append(MOVIE_LANGUAGE_TABLE).append(" ml WHERE m.")
			.append(MOVIE_IDMOVIE).append("='").append(idMovie).append("' AND l.")
			.append(LANGUAGE_CODE).append("='").append(codeLanguage).append("' AND m.")
			.append(MOVIE_IDMOVIE).append("=ml.").append(MOVIE_LANGUAGE_IDMOVIE)
			.append(" AND l.").append(LANGUAGE_CODE).append("=ml.").append(MOVIE_LANGUAGE_LANGUAGE_CODE).append(";");
			
			try {
				ResultSet rs = this.executeQuery(sb.toString());
				if(rs.next()){
					Movie movie = new Movie(rs.getString(1), rs.getString(2));
					movie.setPosterPath(rs.getString(3));
					movie.setHomepage(rs.getString(4));
					movie.setRuntime(rs.getInt(5));
					movie.setReleaseDate(rs.getDate(6));
					movie.setOverview(rs.getString(9));
					movie.setAlternativeName(new AlternativeTitle(new Language(rs.getString(7), rs.getString(8)), rs.getString(10)));
					movie.setTagline(rs.getString(11));
					rs.close();
					
					List<Genre> genres = this.getMovieGenres(movie.getId());
					for (Genre genre : genres) {
						movie.addGenre(genre);
					}
					
					List<Production> productions = this.getMovieProductions(movie.getId());
					for (Production production : productions) {
						movie.addProduction(production);
					}
					
					List<Country> productionCountries = this.getMovieCountriesProduction(movie.getId());
					for (Country country : productionCountries) {
						movie.addProductionCountry(country);
					}
					
					this.logger.info("Retrieved the movie: "+movie);
					return movie;
				}
				else{
					rs.close();
					this.logger.info("The movie with id "+idMovie+" has not a relation with the language: "+codeLanguage);
					return null;
				}
			} catch (SQLException e) {
				throw new JDBCStatementException("Error retrieving the information about the movie :"+idMovie, e);
			}
		}
		else{
			this.logger.info("The movie with id "+idMovie+" does not exist");
			return null;
		}
	}

	@Override
	public List<Movie> getMovies(List<String> ids, String codeLanguage) throws JDBCConnectionException, JDBCStatementException {
		List<String> existingIds = this.existMovies(ids);
		List<Movie> movies = new ArrayList<Movie>();
		Movie movie;
		for (String id : existingIds) {
			ids.remove(id);
			movie = this.getMovie(id, codeLanguage);
			if(movie!=null)
				movies.add(movie);
			else
				this.logger.info("The movie '"+movie+"' is not on the database. Probably not with the selected language");
		}
		
		for (String id : ids) {
			this.logger.info("The movie with id: "+id+" doesn't exist");
		}
		
		return movies;
	}

	@Override
	public boolean deleteMovie(String id, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		if(useTransaction){
			this.beginTransaction();
			this.prepareConnection();
		}
		
		try{
			StringBuilder sb = new StringBuilder("DELETE FROM ");
			sb.append(MOVIE_TABLE)
			.append(" WHERE ").append(MOVIE_IDMOVIE).append("='").append(id).append("');");
			
			int result = this.executeUpdate(sb.toString());

			if(useTransaction) this.commit();
			if(result>0) this.logger.info("Deleted movie: "+id);
			else this.logger.info("Not deleted any movie. Movie: "+id+" not found");
			return result>0;
		}
		catch(JDBCConnectionException except){
			if(useTransaction) this.rollback();
			throw new JDBCStatementException("Error deleting the movie: "+id, except);
		}
		catch(JDBCStatementException except){
			if(useTransaction) this.rollback();
			throw new JDBCStatementException("Error deleting the movie: "+id, except);
		}
	}

	@Override
	public boolean traceMovie(String id, boolean useTransaction) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean addGenre(Genre genre, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		if(genre == null)
			throw new NullPointerException("Parameter genre must not be null");
		
		try {
			if(!this.existsGenre(genre.getId())){
				if(useTransaction){
					this.prepareConnection();
					this.beginTransaction();
				}
				
				StringBuilder sb = new StringBuilder("INSERT INTO ");
				sb.append(GENRE_TABLE).append(" (")
				.append(GENRE_ID).append(", ").append(GENRE_NAME).append(") VALUES (")
				.append(genre.getId()).append(", '").append(genre.getName()).append("');");

				int result = this.executeUpdate(sb.toString());
				this.logger.info("Stored new genre: "+genre);
				if(useTransaction) this.commit();
				return result>0;
			}
			else{
				this.logger.info("Genre not stored, it already exists: "+genre);
				return false;
			}
		} catch (JDBCStatementException e) {
			this.logger.warning("Error adding genre: '"+genre+"'");
			if(useTransaction) this.rollback();
			throw e;
		} catch (JDBCConnectionException e) {
			this.logger.warning("Error adding genre: '"+genre+"'");
			if(useTransaction) this.rollback();
			throw e;
		}
	}

	@Override
	public int addGenres(List<Genre> genres) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		this.logger.info("Adding new genres");
		int rowCount = 0;
		
		List<Integer> ids = new ArrayList<Integer>();
		for (Genre genre : genres) {
			ids.add(genre.getId());
		}

		//Removing existing genres
		List<Integer> existingIds = this.existGenres(ids);
		/*List<Genre> newGenres = genres.subList(0, genres.size());
		for (Integer id : existingIds) {
			for (Genre genre : genres) {
				if(genre.getId()==id){
					this.logger.info("Genre: '"+genre+"' will not be added, it already exists");
					newGenres.remove(genre);
				}
			}
		}*/
		
		//FIX THIS CRAP!!!
		Genre[] newGenres = new Genre[genres.size()];
		genres.toArray(newGenres);
		for (Integer id:existingIds) {
			for (int i = 0; i < newGenres.length; i++) {
				if(newGenres[i]!=null && newGenres[i].getId()==id) newGenres[i]=null;
			}
		}

		for (int i = 0; i < newGenres.length; i++) {
			try{
				if(newGenres[i]!=null){
					if(this.addGenre(newGenres[i], true)){
						rowCount++;
						this.logger.info("Added new genre: "+newGenres[i]);
					}
				}
			}
			catch(JDBCTransactionException except){
				this.logger.warning("Error storing the genre: '"+newGenres[i]+"'\n"+except.getMessage());
			} catch(JDBCStatementException except){
				this.logger.warning("Error storing the genre: '"+newGenres[i]+"'\n"+except.getMessage());
			} catch(JDBCConnectionException except){
				this.logger.warning("Error storing the genre: '"+newGenres[i]+"'\n"+except.getMessage());
			}
		}
		
		return rowCount;
	}

	@Override
	public boolean existsGenre(int id) throws JDBCStatementException, JDBCConnectionException {
		boolean result = false;
		ResultSet rs;
		
		StringBuilder sb = new StringBuilder("SELECT count(*) FROM ");
		sb.append(GENRE_TABLE).append(" WHERE ")
		.append(GENRE_ID).append("=").append(id);
		
		try {
			rs = this.executeQuery(sb.toString());
			if(rs.next())
				result = rs.getInt(1)>0;
			else
				result = false;
			rs.close();
		} catch (SQLException e) {
			throw new JDBCStatementException("Error while checking if a genre is stored", e);
		}
		
		return result;
	}

	@Override
	public List<Integer> existGenres(List<Integer> ids) throws JDBCStatementException, JDBCConnectionException {
		//Not used the method existGenre because it's not optimized for iterations
		
		if(ids !=null){
			ResultSet rs;
			List<Integer> existingIds = new ArrayList<Integer>();
			if(ids.size()==0) return existingIds;
			
			StringBuilder sb = new StringBuilder("SELECT ");
			sb.append(GENRE_ID).append(" FROM ").append(GENRE_TABLE)
			.append(" WHERE ").append(GENRE_ID).append(" IN (");
			for (Integer integer : ids) {
				sb.append(integer).append(",");
			}
			sb.deleteCharAt(sb.length()-1);//delete the last ,
			sb.append(");");
			
			try {
				rs = this.executeQuery(sb.toString());
				while(rs.next()){
					existingIds.add(rs.getInt(1));
				}
				
				rs.close();
				return existingIds;
			} catch (SQLException e) {
				throw new JDBCStatementException("Error checking existing genres", e);
			}
		}
		else
			throw new IllegalArgumentException("Checking existing genres error. The id list must not be null or empty");
	}

	@Override
	public Genre getGenre(int id) throws JDBCStatementException, JDBCConnectionException {
		Genre genre = null;
		try {
			if(!this.existsGenre(id)){
				ResultSet rs;
				
				StringBuilder sb = new StringBuilder("SELECT ");
				sb.append(GENRE_ID).append(", ").append(GENRE_NAME)
				.append(" FROM ").append(GENRE_TABLE)
				.append(" WHERE ").append(GENRE_ID).append("=").append(id);
				
				rs = this.executeQuery(sb.toString());
				if(rs.next()) genre = new Genre(rs.getInt(1), rs.getString(2));
				rs.close();
				if(genre==null) this.logger.info("The genre: "+id+" was not found");
				else this.logger.info("Retrieved the genre: "+genre);
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving the genre: "+id, e);
		}
		
		return genre;
	}
	
	@Override
	public List<Genre> getMovieGenres(String idMovie) throws JDBCConnectionException, JDBCStatementException {
		StringBuilder sb = new StringBuilder("SELECT g.");
		sb.append(GENRE_ID).append(", g.").append(GENRE_NAME).append(" FROM ")
		.append(GENRE_TABLE).append(" g, ").append(MOVIE_TABLE).append(" m, ").append(MOVIE_GENRE_TABLE).append(" mg WHERE m.")
		.append(MOVIE_IDMOVIE).append("='").append(idMovie).append("' AND g.").append(GENRE_ID).append("=mg.").append(MOVIE_GENRE_IDGENRE).append(";");
		
		ResultSet rs = this.executeQuery(sb.toString());
		List<Genre> genres = new ArrayList<Genre>();
		try {
			if(rs.isBeforeFirst()){
				while(rs.next()){
					genres.add(new Genre(rs.getInt(1), rs.getString(2)));
				}
				rs.close();
				return genres;
			}
			else{
				rs.close();
				this.logger.info("No genres found associated to the movie: "+idMovie);
				return genres;
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving the genres associated to the movie: "+idMovie, e);
		}
	}

	@Override
	public boolean deleteGenre(int id, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		if(useTransaction)
			this.beginTransaction();

		StringBuilder sb = new StringBuilder("DELETE FROM ");
		sb.append(GENRE_TABLE)
		.append(" WHERE ").append(GENRE_ID).append("=").append(id);
	
		try{	
			int result = this.executeUpdate(sb.toString());

			if(useTransaction) this.commit();
			if(result>0) this.logger.info("Deleted genre: "+id);
			else this.logger.info("Not deleted any genre. Genre: "+id+" not found");
			return result>0;
		}
		catch(JDBCStatementException except){
			if(useTransaction) this.rollback();
			throw new JDBCStatementException("Error deleting the genre: "+id, except);
		}
	}

	@Override
	public boolean addProduction(Production production, boolean useTransaction) throws JDBCStatementException, JDBCTransactionException, JDBCConnectionException {
		if(production == null)
			throw new NullPointerException("Parameter production must not be null");
		
		if(useTransaction){
			this.prepareConnection();
			this.beginTransaction();
		}
		
		if(!this.existsProduction(production.getId())){
			StringBuilder sb = new StringBuilder("INSERT INTO ");
			sb.append(PRODUCTION_TABLE).append(" (")
			.append(PRODUCTION_IDPRODUCTION).append(", ").append(PRODUCTION_NAME).append(") VALUES (")
			.append(production.getId()).append(", '").append(production.getName()).append("');");
			
			int result = this.executeUpdate(sb.toString());
			this.logger.info("Stored new production: "+production);
			this.commit();
			return result>0;
		}
		else{
			this.logger.info("Production not stored, it already exists: "+production);
			this.rollback();
			return false;
		}
	}

	@Override
	public int addProductions(List<Production> productions) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		this.logger.info("Adding new productions");
		int rowCount = 0;
		
		List<Integer> ids = new ArrayList<Integer>();
		for (Production production : productions) {
			ids.add(production.getId());
		}

		//Removing existing productions
		List<Integer> existingIds = this.existProductions(ids);
		/*for (Integer id : existingIds) {
			for (Production production : productions) {
				if(production.getId()==id)
					productions.remove(production);
			}
		}*/
		Production[] newProductions = new Production[productions.size()];
		productions.toArray(newProductions);
		for (Integer id : existingIds) {
			for (int i = 0; i < newProductions.length; i++) {
				if(newProductions[i]!=null && newProductions[i].getId()==id) newProductions[i]=null;
			}
		}

		for (int i = 0; i < newProductions.length; i++) {
			try{
				if(newProductions[i]!=null){
					if(this.addProduction(newProductions[i], true)){
						rowCount++;
						this.logger.info("Added new production: "+newProductions[i]);
					}
					//false relates a duplicate production, addProduction method shows the logmessage
				}
			}
			catch(JDBCTransactionException except){
				this.logger.warning("Error storing the production: '"+newProductions[i]+"'\n"+except.getMessage());
			} catch(JDBCStatementException except){
				this.logger.warning("Error storing the production: '"+newProductions[i]+"'\n"+except.getMessage());
			} catch(JDBCConnectionException except){
				this.logger.warning("Error storing the production: '"+newProductions[i]+"'\n"+except.getMessage());
			}
		}
				
		return rowCount;
	}

	@Override
	public boolean existsProduction(int id) throws JDBCStatementException, JDBCConnectionException {
		boolean result = false;
		ResultSet rs;
		
		StringBuilder sb = new StringBuilder("SELECT count(*) FROM ");
		sb.append(PRODUCTION_TABLE).append(" WHERE ")
		.append(PRODUCTION_IDPRODUCTION).append("=").append(id);
		
		try {
			rs = this.executeQuery(sb.toString());
			if(rs.next())
				result = rs.getInt(1)>0;
			else
				result = false;
			rs.close();
		} catch (SQLException e) {
			throw new JDBCStatementException("Error while checking if a production is stored", e);
		}
		
		return result;
	}

	@Override
	public List<Integer> existProductions(List<Integer> ids) throws JDBCStatementException, JDBCConnectionException {
		//Not used the method existProduction because it's not optimized for iterations
		
		if(ids !=null){
			ResultSet rs;
			List<Integer> existingIds = new ArrayList<Integer>();
			if(ids.size()==0) return existingIds;

			StringBuilder sb = new StringBuilder("SELECT ");
			sb.append(PRODUCTION_IDPRODUCTION).append(" FROM ").append(PRODUCTION_TABLE)
			.append(" WHERE ").append(PRODUCTION_IDPRODUCTION).append(" IN (");
			for (Integer integer : ids) {
				sb.append(integer).append(",");
			}
			sb.deleteCharAt(sb.length()-1);//delete the last ,
			sb.append(");");

			try {
				rs = this.executeQuery(sb.toString());
				while(rs.next()){
					existingIds.add(rs.getInt(1));
				}

				rs.close();
				return existingIds;
			} catch (SQLException e) {
				throw new JDBCStatementException("Error checking existing productions", e);
			}
		}
		else
			throw new IllegalArgumentException("Checking existing productions error. The id list must not be null or empty");
	}

	@Override
	public Production getProduction(int id) throws JDBCStatementException, JDBCConnectionException {
		Production production = null;
		try {
			if(!this.existsProduction(id)){
				ResultSet rs;
				
				StringBuilder sb = new StringBuilder("SELECT ");
				sb.append(PRODUCTION_IDPRODUCTION).append(", ").append(PRODUCTION_NAME)
				.append(" FROM ").append(PRODUCTION_TABLE)
				.append(" WHERE ").append(PRODUCTION_IDPRODUCTION).append("=").append(id);
				
				rs = this.executeQuery(sb.toString());
				if(rs.next()) production = new Production(rs.getInt(1), rs.getString(2));
				rs.close();
				if(production==null) this.logger.info("Production: "+id+" was not found");
				else this.logger.info("Retrieved the production: "+production);
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving the production: "+id, e);
		}
		
		return production;
	}

	@Override
	public List<Production> getMovieProductions(String idMovie) throws JDBCConnectionException, JDBCStatementException {
		StringBuilder sb = new StringBuilder("SELECT p.");
		sb.append(PRODUCTION_IDPRODUCTION).append(", p.").append(PRODUCTION_IDPRODUCTION).append(" FROM ")
		.append(PRODUCTION_TABLE).append(" p, ").append(MOVIE_TABLE).append(" m, ").append(MOVIE_GENRE_TABLE).append(" mg WHERE m.")
		.append(MOVIE_IDMOVIE).append("='").append(idMovie).append("' AND p.").append(PRODUCTION_IDPRODUCTION).append("=mg.").append(MOVIE_PRODUCTION_IDPRODUCTION).append(";");
		
		ResultSet rs = this.executeQuery(sb.toString());
		List<Production> productions = new ArrayList<Production>();
		try {
			if(rs.isBeforeFirst()){
				while(rs.next()){
					productions.add(new Production(rs.getInt(1), rs.getString(2)));
				}
				rs.close();
				return productions;
			}
			else{
				rs.close();
				this.logger.info("No productions found associated to the movie: "+idMovie);
				return productions;
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving the productions associated to the movie: "+idMovie, e);
		}
	}

	@Override
	public boolean deleteProduction(int id, boolean useTransaction) throws JDBCTransactionException, JDBCConnectionException, JDBCStatementException {
		if(useTransaction)
			this.beginTransaction();
		try{
			StringBuilder sb = new StringBuilder("DELETE FROM ");
			sb.append(PRODUCTION_TABLE)
			.append(" WHERE ").append(PRODUCTION_IDPRODUCTION).append("=").append(id);
			
			int result = this.executeUpdate(sb.toString());

			if(useTransaction) this.commit();
			if(result>0) this.logger.info("Deleted production: "+id);
			else this.logger.info("Not deleted any production. Production: "+id+" not found");
			return result>0;
		}
		catch(JDBCConnectionException except){
			if(useTransaction) this.rollback();
			throw new JDBCStatementException("Error deleting the production: "+id, except);
		}
		catch(JDBCStatementException except){
			if(useTransaction) this.rollback();
			throw new JDBCStatementException("Error deleting the production: "+id, except);
		}
	}

	@Override
	public boolean addCountry(Country country, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		if(country == null)
			throw new NullPointerException("Parameter country must not be null");
		
		if(!this.existsCountry(country.getCode())){
			if(useTransaction){
				this.prepareConnection();
				this.beginTransaction();
			}
			
			StringBuilder sb = new StringBuilder("INSERT INTO ");
			sb.append(COUNTRY_TABLE).append(" (")
			.append(COUNTRY_CODE).append(", ").append(COUNTRY_NAME).append(") VALUES ('")
			.append(country.getCode()).append("', '").append(country.getName()).append("');");
			
			try {
				int result = this.executeUpdate(sb.toString());
				this.logger.info("Stored new country: "+country);
				if(useTransaction) this.commit();
				return result>0;
			} catch (JDBCConnectionException e) {
				this.logger.warning("Error adding country: '"+country+"'");
				if(useTransaction) this.rollback();
				throw e;
			} catch (JDBCStatementException e) {
				this.logger.warning("Error adding country: '"+country+"'");
				if(useTransaction) this.rollback();
				throw e;
			}
		}
		else{
			this.logger.info("Country not stored, it already exists: "+country);
			return false;
		}
	}

	@Override
	public int addCountries(List<Country> countries) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		this.logger.info("Adding new countries");
		int rowCount = 0;
		
		List<String> ids = new ArrayList<String>();
		for (Country country : countries) {
			ids.add(country.getCode());
		}

		//Removing existing countries
		List<String> existingIds = this.existCountries(ids);
		for (String id : existingIds) {
			for (Country country : countries) {
				if(country.getCode()==id)
					this.logger.info("Country: '"+country+"' will not be added, it already exists");
				countries.remove(country);
			}
		}

		for (Country country : countries) {
			try{
				if(this.addCountry(country, true)){
					rowCount++;
					this.logger.info("Added new country: "+country);
				}
			}
			catch(JDBCTransactionException except){
				this.logger.warning("Error storing the country: '"+country+"'\n"+except.getMessage());
			} catch(JDBCStatementException except){
				this.logger.warning("Error storing the country: '"+country+"'\n"+except.getMessage());
			} catch(JDBCConnectionException except){
				this.logger.warning("Error storing the country: '"+country+"'\n"+except.getMessage());
			}
		}
		
		return rowCount;
	}

	@Override
	public boolean existsCountry(String code) throws JDBCStatementException, JDBCConnectionException {
		boolean result = false;
		ResultSet rs;
		
		StringBuilder sb = new StringBuilder("SELECT count(*) FROM ");
		sb.append(COUNTRY_TABLE).append(" WHERE ")
		.append(COUNTRY_CODE).append("='").append(code).append("';");
		
		try {
			rs = this.executeQuery(sb.toString());
			if(rs.next())
				result = rs.getInt(1)>0;
			else
				result = false;
			rs.close();
		} catch (SQLException e) {
			throw new JDBCStatementException("Error while checking if a country is stored", e);
		}
		
		return result;
	}

	@Override
	public List<String> existCountries(List<String> codes) throws JDBCStatementException, JDBCConnectionException {
		//Not used the method existsCountry because it's not optimized for iterations
		
		if(codes != null){
			List<String> existingIds = new ArrayList<String>();
			if(codes.size()==0) return existingIds;
			
			ResultSet rs;
			
			StringBuilder sb = new StringBuilder("SELECT ");
			sb.append(COUNTRY_CODE).append(" FROM ").append(COUNTRY_TABLE)
			.append(" WHERE ").append(COUNTRY_CODE).append(" IN (");
			for (String code : codes) {
				sb.append("'").append(code).append("',");
			}
			sb.deleteCharAt(sb.length()-1);//delete the last ,
			sb.append(");");

			try {
				rs = this.executeQuery(sb.toString());
				while(rs.next()){
					existingIds.add(rs.getString(1));
				}

				rs.close();
				return existingIds;
			} catch (SQLException e) {
				throw new JDBCStatementException("Error checking existing countries", e);
			}
		}
		else
			throw new IllegalArgumentException("Checking existing countries error. The id list must not be null or empty");
	}

	@Override
	public Country getCountry(String code) throws JDBCStatementException, JDBCConnectionException {
		StringBuilder sb = new StringBuilder("SELECT ");
		sb.append(COUNTRY_CODE).append(", ").append(COUNTRY_NAME).append(" FROM ").append(COUNTRY_TABLE).append(" WHERE ").append(COUNTRY_CODE).append("='").append(code).append("';");
		
		ResultSet rs = this.executeQuery(sb.toString());
		try {
			if(rs.next()){
				Country country = new Country(rs.getString(1), rs.getString(2));
				this.logger.info("Retrieved country: "+country);
				rs.close();
				return country;
			}
			else{
				rs.close();
				this.logger.info("Country with id: "+code+" was not found");
				return null;
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving country "+code, e);
		}		
	}

	@Override
	public List<Country> getMovieCountriesProduction(String idMovie) throws JDBCStatementException, JDBCConnectionException {
		StringBuilder sb = new StringBuilder("SELECT c.");
		sb.append(COUNTRY_CODE).append(", c.").append(COUNTRY_NAME).append(" FROM ")
		.append(COUNTRY_TABLE).append(" c, ").append(MOVIE_TABLE).append(" m, ").append(COUNTRY_PRODUCTION_TABLE).append(" pc WHERE m.")
		.append(MOVIE_IDMOVIE).append("='").append(idMovie).append("' AND pc.").append(COUNTRY_PRODUCTION_IDMOVIE).append("=m.").append(MOVIE_IDMOVIE).append(";");
		
		ResultSet rs = this.executeQuery(sb.toString());
		List<Country> countries = new ArrayList<Country>();
		try {
			if(rs.isBeforeFirst()){
				while(rs.next()){
					countries.add(new Country(rs.getString(1), rs.getString(2)));
				}
				rs.close();
				return countries;
			}
			else{
				rs.close();
				this.logger.info("No countries found associated to the movie: "+idMovie);
				return countries;
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving the countries associated to the movie: "+idMovie, e);
		}
	}

	@Override
	public boolean deleteCountry(String code, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		StringBuilder sb = new StringBuilder("DELETE FROM ");
		sb.append(COUNTRY_TABLE).append(" WHERE ").append(COUNTRY_CODE).append("='").append(code).append("';");
		
		if(useTransaction){
			this.prepareConnection();
			this.beginTransaction();
		}
		
		if(this.executeUpdate(sb.toString())>0){
			this.logger.info("Removed from the database the country: "+code);
			if(useTransaction) this.commit();
			return true;
		}
		else{
			this.logger.info("The country has not been removed. Probably it doesn't exist");
			if(useTransaction) this.rollback();
			return false;
		}
	}

	@Override
	public boolean addLanguage(Language language, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		if(language == null)
			throw new NullPointerException("Parameter Language must not be null");
				
		if(!this.existsLanguage(language.getCode())){
			if(useTransaction){
				this.prepareConnection();
				this.beginTransaction();
			}
			
			StringBuilder sb = new StringBuilder("INSERT INTO ");
			sb.append(LANGUAGE_TABLE).append(" (").append(LANGUAGE_CODE).append(LANGUAGE_NAME).append(") VALUES ('").append(language.getCode()).append("', '").append(language.getName()).append("');");
			
			if(this.executeUpdate(sb.toString())>0){
				this.logger.info("Language added: "+language);
				if(useTransaction) this.commit();
				return true;
			}
			else{
				this.logger.info("Language not added: "+language);
				if(useTransaction) this.rollback();
				return false;
			}
		}
		else{
			this.logger.info("Language: '"+language+"' not added, it already exists");
			return false;
		}
	}

	@Override
	public int addLanguages(List<Language> languages) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		this.logger.info("Adding new languages");
		int rowCount = 0;
		
		List<String> ids = new ArrayList<String>();
		for (Language language : languages) {
			ids.add(language.getCode());
		}

		//Removing existing languages
		List<String> existingIds = this.existLanguages(ids);
		for (String id : existingIds) {
			for (Language language : languages) {
				if(language.getCode()==id)
					this.logger.info("Language: '"+language+"' will not be added, it already exists");
				languages.remove(language);
			}
		}

		for (Language language : languages) {
			try{
				if(this.addLanguage(language, true)){
					rowCount++;
					this.logger.info("Added new language: "+language);
				}
			}
			catch(JDBCTransactionException except){
				this.logger.warning("Error storing the language: '"+language+"'\n"+except.getMessage());
			} catch(JDBCStatementException except){
				this.logger.warning("Error storing the country: '"+language+"'\n"+except.getMessage());
			} catch(JDBCConnectionException except){
				this.logger.warning("Error storing the country: '"+language+"'\n"+except.getMessage());
			}
		}
		
		return rowCount;
	}

	@Override
	public boolean existsLanguage(String code) throws JDBCStatementException, JDBCConnectionException {
		boolean result = false;
		ResultSet rs;
		
		StringBuilder sb = new StringBuilder("SELECT count(*) FROM ");
		sb.append(LANGUAGE_TABLE).append(" WHERE ")
		.append(LANGUAGE_CODE).append("='").append(code).append("';");
		
		try {
			rs = this.executeQuery(sb.toString());
			if(rs.next())
				result = rs.getInt(1)>0;
			else
				result = false;
			rs.close();
		} catch (SQLException e) {
			throw new JDBCStatementException("Error while checking if a language is stored", e);
		}
		
		return result;
	}

	@Override
	public List<String> existLanguages(List<String> codes) throws JDBCStatementException, JDBCConnectionException {
		//Not used the method existsLanguage because it's not optimized for iterations
		
		if(codes != null){
			ResultSet rs;
			List<String> existingIds = new ArrayList<String>();
			if(codes.size()==0) return existingIds;

			StringBuilder sb = new StringBuilder("SELECT ");
			sb.append(LANGUAGE_CODE).append(" FROM ").append(LANGUAGE_TABLE)
			.append(" WHERE ").append(LANGUAGE_CODE).append(" IN (");
			for (String code : codes) {
				sb.append("'").append(code).append("',");
			}
			sb.deleteCharAt(sb.length()-1);//delete the last ,
			sb.append(");");

			try {
				rs = this.executeQuery(sb.toString());
				while(rs.next()){
					existingIds.add(rs.getString(1));
				}

				rs.close();
				return existingIds;
			} catch (SQLException e) {
				throw new JDBCStatementException("Error checking existing languages", e);
			}
		}
		else
			throw new IllegalArgumentException("Checking existing languages error. The id list must not be null or empty");
	}

	@Override
	public Language getLanguage(String code) throws JDBCStatementException, JDBCConnectionException {
		StringBuilder sb = new StringBuilder("SELECT ");
		sb.append(LANGUAGE_CODE).append(", ").append(LANGUAGE_NAME).append(" FROM ").append(LANGUAGE_TABLE).append(" WHERE ").append(LANGUAGE_CODE).append("='").append(code).append("';");
		
		ResultSet rs = this.executeQuery(sb.toString());
		try {
			if(rs.next()){
				Language language = new Language(rs.getString(1), rs.getString(2));
				this.logger.info("Retrieved language: "+language);
				rs.close();
				return language;
			}
			else{
				rs.close();
				this.logger.info("Language with id: "+code+" was not found");
				return null;
			}
		} catch (SQLException e) {
			throw new JDBCStatementException("Error retrieving Language "+code, e);
		}	
	}

	@Override
	public boolean deleteLanguage(String code, boolean useTransaction) throws JDBCTransactionException, JDBCStatementException, JDBCConnectionException {
		StringBuilder sb = new StringBuilder("DELETE FROM ");
		sb.append(LANGUAGE_TABLE).append(" WHERE ").append(LANGUAGE_CODE).append("='").append(code).append("';");
		
		if(useTransaction){
			this.prepareConnection();
			this.beginTransaction();
		}
		
		if(this.executeUpdate(sb.toString())>0){
			this.logger.info("Removed from the database the language: "+code);
			if(useTransaction) this.commit();
			return true;
		}
		else{
			this.logger.info("The language has not been removed. Probably it doesn't exist");
			if(useTransaction) this.rollback();
			return false;
		}
	}
}